package org.flower.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.flower.dao.CommodityClassDao;
import org.flower.entity.CommodityClass;
import org.flower.utils.BaseDao;

public class CommodityClassDaoImpl extends BaseDao implements CommodityClassDao {

	//根据父级编号获取商品类别信息
	@Override
	public List<CommodityClass> getCommodClassByPid(int pid) {
		String sql = "select id,`name`,pid,`type`,image,ahref from commodityClass where pid=?";
		ResultSet rs = super.executeQuery(sql,pid);
		List<CommodityClass> comlist = new ArrayList<CommodityClass>();
		try {
			while(rs.next()){
				CommodityClass com = new CommodityClass(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				comlist.add(com);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				super.closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return comlist;
	}

	@Override
	public String getClassNameById(int classId) {
		String className = "";
		String sql = "SELECT * FROM commodityclass WHERE id = ?";
		ResultSet rs = executeQuery(sql, classId);
		try {
			if(rs.next()){
				className = rs.getString("name");
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			try {
				closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
		return className;
	}

	@Override
	public String findPname(int id) {
		String pName = "";
		String sql = "SELECT NAME FROM commodityclass WHERE id = (SELECT pid FROM commodityclass WHERE id = ?)";
		ResultSet rs = executeQuery(sql, id);
		try {
			if (rs.next()){
				pName = rs.getString("name");
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			try {
				closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	return pName;

	}

	@Override
	public boolean addCommodityClass(CommodityClass c) {
		String sql = "INSERT INTO commodityclass VALUES (NULL,?,?,?,?,?)";
		return executeUpdate(sql,c.getName(),c.getPid(),c.getType(),c.getImage(),c.getAhref());
	}

	@Override
	public int getTypeById(int id) {
		int result = 0;
		String sql = "SELECT TYPE FROM commodityclass WHERE id = ?";
		ResultSet rs = executeQuery(sql, id);
		try {
			if(rs.next()){
				result = rs.getInt("type");
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			try {
				closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
		return result;
	}

	@Override
	public boolean deleteCommodityClassById(int id) {
		String sql = "delete from commodityClass where id = ?";
		return executeUpdate(sql, id);
	}

	@Override
	public CommodityClass getCommodityClassById(int id) {
		CommodityClass commodityClass = null;
		String sql = "SELECT * FROM commodityclass WHERE id= ?";
		ResultSet rs = executeQuery(sql, id);
		try {
			if (rs.next()){
				commodityClass = new CommodityClass(rs.getInt("id"),rs.getString("name"),rs.getInt("pid"),rs.getInt("type"),rs.getString("image"),rs.getString("ahref"));
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		return commodityClass;
	}

	@Override
	public boolean updateCommodityClass(CommodityClass c) {
		String sql = "UPDATE commodityclass SET name=?,pid=?,type=?,image=?,ahref=? WHERE id = ?";
		return executeUpdate(sql,c.getName(),c.getPid(),c.getType(),c.getImage(),c.getAhref(),c.getId());
	}

	@Override
	public List<CommodityClass> getAllCommodityClass() {
		String sql = "SELECT id,`name` FROM commodityclass WHERE id IN(SELECT classId FROM commoditydetails GROUP BY classId)";
		ResultSet rs = super.executeQuery(sql);
		List<CommodityClass> comlist = new ArrayList<CommodityClass>();
		try {
			while (rs.next()){
				CommodityClass c = new CommodityClass();
				c.setId(rs.getInt(1));
				c.setName(rs.getString(2));
				comlist.add(c);
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			try {
				closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
		return comlist;
	}

}
